package jhc.cn.db;

import java.sql.SQLException;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import jhc.cn.AirSpace;
import jhc.cn.bean.Article;

public class ArticleDao {
	
	public static void add(QueryRunner runner, Article article) throws SQLException {
		final String sql = "insert into article(title,content,publishDate,userID) "
				+ "values(?,?,?,?)";
		runner.execute(sql, article.getTitle(), article.getContent(), article.getPublishDate(), article.getUserID());
	}

	public static List<Article> find(QueryRunner runner, int pageNum) throws SQLException {
		final String sql = "select a.id,a.title,a.content,a.publishDate,a.userID, b.username from article a,user b  "
				+ "where a.userID = b.ID  order by a.id asc limit ? offset ?";
		return runner.query(sql, new BeanListHandler<>(Article.class), 
				AirSpace.PAGE_SIZE, pageNum * AirSpace.PAGE_SIZE);
	}
	
	public static void del(QueryRunner runner, int id) throws SQLException {
		final String sql = "delete from article where id = ?";
		runner.execute(sql, id);
	}
	
	public static long count(QueryRunner runner) throws SQLException {
		final String sql = "select count(id) from article";
		return runner.query(sql, new ScalarHandler<Long>());
	}
	
	
}
